package cn.vortex.device.dao.impl;

import cn.vortex.device.dao.BaseDao;
import cn.vortex.device.dao.DeviceDao;
import cn.vortex.device.tool.Strings;
import cn.vortex.device.vo.Device;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class DeviceDaoImpl extends AbstractBaseDaoImpl<Device> implements BaseDao<Device,Integer>, DeviceDao {

    //MySQL
    //private static final String BASE_FIELD = " id,\"number\",name,category,specs,price,factory,QGP,state,buyTime,doPerson ";

    //Oracle
    private static final String BASE_FIELD = " id,\"number\",name,category,specs,price,factory,QGP,state,buyTime,doPerson ";

    @Override
    public List<Device> findAll() throws Exception {

        String sql = "select" + BASE_FIELD + "from t_device";
        return findListObj(sql, Device.class,null);
    }

    @Override
    public int update(Device device) throws Exception {

        String sql = "update t_device set name = ?,factory = ?,specs = ?," +
                "price = ?,QGP = ?,state = ? where id = ?";

        return queryRunner.update(sql,device.getName(),device.getFactory(),device.getSpecs(),
                device.getPrice(),device.getQGP(),device.getState(),device.getId());
    }

    @Override
    public int insert(Device device) throws Exception {
        //MySQL
        //String sql = "insert into t_device(name,QGP,price,specs,factory,category,buyTime,doPerson,cid,number) values " +
        //        "(?,?,?,?,?,?,?,?,?,?)";
        //Oracle
        //String sql = "insert into t_device(name,QGP,price,specs,factory,category,buyTime,doPerson,cid,\"number\") values " +
        //        "(?,?,?,?,?,?,to_Date(?, 'YYYY-MM-DD HH24:MI:SS'),?,?,?)";
        String sql = "{call proc_add_device(?,?,?,?,?,?,?,?,to_Date(?, 'YYYY-MM-DD HH24:MI:SS'),?,?)}";

        String buyDateStr = Strings.getOracleDateFormat(device.getBuyTime());
        System.out.println(buyDateStr);

        return queryRunner.update(sql,device.getName(),device.getNumber(),device.getCid(),
                device.getCategory(),device.getSpecs(),device.getPrice(),device.getFactory(),
                device.getQGP(),buyDateStr,device.getDoPerson(),java.sql.Types.INTEGER);
    }

    @Override
    public int deleteById(Integer id) throws Exception {
        //String sql = "delete from t_device where id = ?";

        String sql = "{call proc_delete_device(?, ?)}";
        return queryRunner.update(sql,id, Types.INTEGER);
    }

    @Override
    public int deleteByIdAndUser(Integer id, String user) throws Exception {
        String sql = "delete from t_device where id = ? and user = ?";
        return queryRunner.update(sql, id, user);
    }

    @Override
    public Device findById(Integer id) throws Exception {
        String sql = "select"+BASE_FIELD+"from t_device where id = ?";
        return findObject(sql,Device.class,id);
    }

    public List<Map<String,Object>> getPieData()throws Exception{
        //MySQL
        //String sql = "select category as name, count(1) as value " +
        //        "from t_device where state != 2 group by category;";
        //Oracle
        String sql = "select category as \"name\", count(1) as \"value\" " +
                "from t_device where state != 2 group by category";

        return queryRunner.query(sql,new MapListHandler());
    }

    public List<Map<String,Object>> getDeviceValueData()throws Exception{
        //MySQL
        //String sql = "select category as name,sum(price) as data " +
        //        "from t_device where state != 2 group by category";

        //Oracle
        //String sql = "select category as \"name\",to_char(sum(price),'fm9999990.00') as \"data\" " +
        //        "from t_device where state != 2 group by category";
        String sql = "select category as \"name\",sum(price) as \"data\" " +
                "from t_device where state != 2 group by category";

        return queryRunner.query(sql,new MapListHandler());
    }

    /**
     * 从字符串中分理处数字
     * @param str
     * @return
     */
    private String getNum(String str){
        String regex="[^0-9]";
        Pattern p = Pattern.compile(regex);
        Matcher m = p.matcher(str);
        return m.replaceAll("").trim();
    }



}
